package batch.promionet;

import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.ObjectUtils;
import org.apache.log4j.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.*;
import java.io.*;
import java.util.Date;
import java.util.Calendar;

public class promionetUserNoFile
{
    final String TODAY = DateFormatUtils.format(new Date(), "yyyyMMdd");
    final String FILEPATH = "/home01/sfc/batch/promionet/data/";

    private OutputStreamWriter oswForData;
    private int                j = 0;

    String strCountry = "";  //Which Country
    String strLang    = "";  //Which Language
    String strAddName = "Premio_WWW";  //Temp File Name
    String dataFileNm = "";

    public promionetUserNoFile(String strPrCountry, String strPrLang) {
        strCountry = StringUtils.defaultString(strPrCountry);
        strLang    = StringUtils.defaultString(strPrLang);
    }

    public static void main(String [] args) {
        Logger.getLogger("promionet.makeuserno").info("( * ) Start.");

        promionetUserNoFile ptUserNo = null;

        if( args.length == 2 ){
            ptUserNo = new promionetUserNoFile(args[0], args[1]);
        }
        else{
            System.exit(-1);
        }

        ptUserNo.startMake();

        Logger.getLogger("promionet.makeuserno").info("( * ) End.");
    }

    //Make a log file and a data file.
    private void startMake(){
        dataFileNm = "DataOfUserNo_" + TODAY;

        if(strCountry.length() > 0){
            dataFileNm += "_" + strCountry;
        }
        else{
            dataFileNm += "_all";
        }

        if(strLang.length() > 0){
            dataFileNm += "_" + strLang;
        }

        if(strAddName.length() > 0){
            dataFileNm += "_" + strAddName;
        }

        dataFileNm += ".txt";

        try {
            //To make Data File by 'UTF-8'
            oswForData = new OutputStreamWriter(new FileOutputStream(FILEPATH + dataFileNm, false), "UTF-8");

            getUsers();

        }
        catch(Exception ex) {
            ex.printStackTrace();
            Logger.getLogger("promionet.makeuserno").info(ex.toString());
        }
        finally {
            if( oswForData != null ) try { oswForData.close(); } catch(Exception ex) { ex.printStackTrace(); }
        }
    }

    //Obtain Users from DB.
    private void getUsers() throws Exception{
        Connection         conn  = null;
        PreparedStatement  pstmt = null;
        ResultSet          rs    = null;

        String  strID           = "";
        String  strFullSt       = "";

        StringBuffer strBuffer = new StringBuffer()
                                     .append("SELECT  USER_NO                                                                                                     \n")
                                     .append("  FROM  G_MB_MEMBER                                                                                                 \n")
                                     .append(" WHERE (LTRIM(RTRIM(EMAIL)) IS NOT NULL AND EMAIL <> 'WAP_TEMP')                                                    \n")
                                     .append("   AND  DELETED IS NULL                                                                                             \n");

        //According to Renewal
        if(   !strCountry.equals("gbr")
           && !strCountry.equals("ger")
           && !strCountry.equals("nld")
           && !strCountry.equals("rus")
           && !strCountry.equals("bel")
           && !strCountry.equals("ita")
           && !strCountry.equals("prt")
           && !strCountry.equals("nrd")
           && !strCountry.equals("esp")
           && !strCountry.equals("uan")){
            strBuffer.append("                   AND  NEWS_LETTER = 'Y'                                                                                           \n");
        }
        else{
            strBuffer.append("                   AND  USER_NO IN (SELECT USER_NO FROM G_MB_OPTIN WHERE CODE = '0113001')                                          \n");
        }

        if (strCountry.equals("bel")) {
            strBuffer.append("                   AND  COUNTRY <> '0102042'                                                                                        \n");
        }

        if(!strLang.equals("")){
            if(strCountry.equals("eur")){  //To regard 'NULL' as 'ENG' only for eur
                strBuffer.append("               AND  '" + strLang + "' = DECODE(LANGUAGE, 'ENG', 'ENG', 'GER', 'GER', 'ENG')                                     \n");
            }
            else if(strCountry.equals("uan")){  //To regard 'NULL' as 'RUS' only for uan
                strBuffer.append("               AND  '" + strLang + "' = DECODE(LANGUAGE, 'RUS', 'RUS', 'UKR', 'UKR', 'RUS')                                     \n");
            }
            else if(strCountry.equals("bel")){  //To regard 'NULL' as 'NLD' only for bel
                strBuffer.append("               AND  '" + strLang + "' = DECODE(LANGUAGE, 'NLD', 'NLD', 'FRA', 'FRA', 'NLD')                                     \n");
            }
            else{
                strBuffer.append("               AND  LANGUAGE = '" + strLang + "'                                                                                \n");
            }
        }

        if(!strCountry.equals("")){
            strBuffer.append("                   AND  REG_COUNTRY = '" + strCountry + "'                                                                          \n");
        }
        else{
            strBuffer.append("                   AND  REG_COUNTRY IN ('ger', 'eur', 'esp', 'nld', 'prt', 'rus', 'uan', 'bel', 'ita', 'grc', 'nrd', 'fra', 'gbr')  \n");
        }

        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection("jdbc:oracle:thin:@eu-anydb:1521:ORAEU", "gsms", "any02gs");

            //Add Query
            strBuffer.append(this.addQuery());

			System.out.println("Query: " + strBuffer);

            pstmt = conn.prepareStatement(strBuffer.toString());
            rs = pstmt.executeQuery();

            while(rs.next()){
                strID = ObjectUtils.toString(rs.getObject(1), "");

                //Make Sentences
                strFullSt = strID + "\n";

                //Write down sentences on the file
                oswForData.write(strFullSt);

                j++;
            }

            Logger.getLogger("promionet.makeuserno").info("Total Count : [" + dataFileNm + "] - " + j);
        }
        finally {
            if(rs != null)    try { rs.close(); }    catch (Exception ex) { ex.printStackTrace(); }
            if(pstmt != null) try { pstmt.close(); } catch (Exception ex) { ex.printStackTrace(); }
            if(conn != null)  try { conn.close(); }  catch (Exception ex) { ex.printStackTrace(); }
        }
    }

    private StringBuffer addQuery(){
        Calendar calen = Calendar.getInstance();
        calen.add(Calendar.DATE, -1);
        Date ye1day = calen.getTime();

        String YESTERDAY = DateFormatUtils.format(ye1day, "yyyyMMdd");

        StringBuffer addQuery = new StringBuffer()
                               //.append("         AND  CREATED < TO_DATE('" + TODAY + "000000', 'YYYYMMDDHH24MISS') \n")

                               //.append("         AND  CREATED BETWEEN TO_DATE('" + YESTERDAY + "000000', 'YYYYMMDDHH24MISS') \n")
                               //.append("                          AND TO_DATE('" + YESTERDAY + "235959', 'YYYYMMDDHH24MISS') \n")

                               //.append("         AND  NVL(MEMBERGBN, 'M') = '10440102' \n");  //10440102


                               //.append("           AND COUNTRY IN ('0102034', '0102036', '0102015') \n");
                               .append("         AND  NVL(MEMBERGBN, 'M') = '10500101'     \n");  //10484101

                               //.append("         AND  NVL(MEMBERGBN, 'M') = '10260101' \n");  //Adpepper
                               //.append("         AND  NVL(MEMBERGBN, 'M') IN ('10373101', '10260102') \n"); //Midao
                               //.append("         AND  NVL(MEMBERGBN, 'M') IN ('10323101', '10287101', '10231101') \n");  //eCircle
                               //.append("         AND  NVL(MEMBERGBN, 'M') IN ('10399101', '10372101') \n");  //Boungiorno

                               //.append("         AND  NVL(MEMBERGBN, 'M') NOT LIKE '10315%'                                                                      \n")
                               //.append("         AND  NVL(HP_BRAND, '0103001') = '0103002'                                                                       \n")
                               //.append("         AND  DECODE(GENDER, 'F', 'F', 'M', 'M', 'M') = 'F'                                                              \n")
                               //.append("         AND  DECODE(HP_MODEL, NULL, '1', HP_MODEL) NOT IN ('SGH-D500', 'SGH-D600', 'SGH-E720')                          \n")
                               //.append("         AND  DECODE(HP_MODEL, NULL, '1', HP_MODEL) = 'SGH-E720'                                                         \n");

        return addQuery;
    }
}
